package com.lizx.admin.mapper;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lizx.pojo.Expenditure;
import com.lizx.pojo.vo.ExpenditureStatistics;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
* @author lizexing
* @description 针对表【tb_expenditure】的数据库操作Mapper
* @createDate 2024-04-29 10:31:37
* @Entity generator.pojo.Expenditure
*/
@Mapper
public interface ExpenditureMapper extends BaseMapper<Expenditure> {

//    @Select("<script>select any_value(ex.create_time) create_time,ex.aid,any_value(ex.aname) aname,sum(ex.price) price from tb_expenditure ex" +
//            "          WHERE year(ex.create_time) = #{year} AND MONTH(ex.create_time) = #{month} AND ex.`status` = 2 and ex.data_archiving = #{archiving} " +
//                    " <if test='areaName!=\"\"'>AND ex.aname= #{areaName}</if>" +
//            "  GROUP BY ex.aid </script>")
//    @Select("<script>select any_value(ex.create_time) create_time,ex.aid,any_value(ex.aname) aname,sum(ex.price) price from tb_expenditure ex" +
//            "          WHERE  ex.`status` = 2 and data_ar_id = #{archId} " +
//            " <if test='areaName!=\"\"'>AND ex.aname= #{areaName}</if>" +
//            "  GROUP BY ex.aid </script>")

    @Select("<script>select any_value(ex.create_time) create_time,ex.aid,any_value(ex.aname) aname,sum(ex.price) price " +
            " from tb_expenditure ex WHERE ex.`status` = 2 and data_ar_id = #{archId} " +
            " <if test='areaName!=\"\"'>AND ex.aname= #{areaName}</if>" +
            "  GROUP BY YEAR(ex.create_time), MONTH(ex.create_time),ex.aid </script>")
    List<ExpenditureStatistics> statisticsList(@Param("areaName")String areaName,@Param("archId") Integer archId);

    @Select("<script>select * from tb_expenditure ex where ex.aid = #{aid} " +
            "AND ex.status = 2 AND year(ex.create_time) = #{year} AND MONTH(ex.create_time) = #{month} " +
            " <if test='employeeName!=\"\"'>AND ex.uname= #{employeeName}</if>" +
            " <if test='projectName!=\"\"'>AND ex.project_name= #{projectName}</if>" +
            " AND ex.data_archiving= #{archiving} " +
            " limit #{start},#{end}" +
            "</script>")
    List<Expenditure> detail(@Param("start")Integer start,@Param("end") int end,
                             @Param("aid")Integer aid, @Param("year")int year,@Param("month")int month,
                             @Param("employeeName")String employeeName, @Param("projectName")String projectName,@Param("archiving")Integer archiving);

    @Select("<script>select * from tb_expenditure ex where ex.aid = #{aid} " +
            "AND ex.status = 2 AND year(ex.create_time) = #{year} AND MONTH(ex.create_time) = #{month} " +
            " <if test='employeeName!=\"\"'>AND ex.uname= #{employeeName}</if>" +
            " <if test='projectName!=\"\"'>AND ex.project_name= #{projectName}</if>" +
            "</script>")
    List<Expenditure> detailList(Integer aid, int year, int month, String employeeName, String projectName);

    @Select("SELECT sum(price) FROM tb_expenditure e WHERE e.create_time >= #{starDate} " +
            "and e.create_time <= #{endDate} and e.`status` = #{status}")
    Integer sumTotalFee(@Param("starDate")Date starDate,@Param("endDate") Date endDate,@Param("status") int status);
}




